package io.gitee.hejiang.action.impl;

import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.nutz.dao.impl.sql.SwaggerSqlNutSql;
import org.nutz.dao.sql.Sql;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fasterxml.jackson.databind.JavaType;

import io.gitee.hejiang.action.SwaggerSqlAction;
import io.gitee.hejiang.action.SwaggerSqlActionProvider;
import io.swagger.inflector.models.RequestContext;

public class SqlActionProvider implements SwaggerSqlActionProvider {
	private static Logger _logger = LoggerFactory.getLogger(SqlAction.class);

	@Override
	public SwaggerSqlAction createAction(Map<String, Object> config) {
		String type = (String) config.get("type");
		if ("sql".equalsIgnoreCase(type) || config.containsKey("sql")) {
			return new SqlAction();
		}
		return null;
	}

	static class SqlAction implements SwaggerSqlAction {
		Map<String, JavaType> _argumentTypes;
		String _key;
		SwaggerSqlAction _next;
		List<String> _sqlTexts;

		/**
		 * 有时候某些参数是数组, 那么这些数组参数怎么使用? 对于 sql 语句, 我们可以通过 loop (循环)来控制。 
		 * 例如传入两个数组参数和一个非数组参数:
		 * a = [1,2], b = [2,3,4], c = 1
		 * 
		 * 我们可以进行以下设置:
		 * - loop:
		 *   - [a, b]
		 * 那么, sql 语句会执行三遍, 分别使用以下参数:
		 * 第一遍 a = 1, b = 2, c = 1;
		 * 第二遍 a = 2, b = 3, c = 1;
		 * 第三遍 a = 1, b = 4, c = 1;
		 * 
		 * 如果我们设置为:
		 * - loop:
		 *   - a
		 *   - b
		 * 那么 sql 语句则会执行六遍, 分别使用以下参数:
		 * a = 1, b = 2, c = 1;
		 * a = 1, b = 3, c = 1;
		 * a = 1, b = 4, c = 1;
		 * a = 2, b = 2, c = 1;
		 * a = 2, b = 3, c = 1;
		 * a = 2, b = 4, c = 1;
		 */
		List<List<String>> _loop;

		@SuppressWarnings("unchecked")
		private List<Map<String, Object>> getArgMapArray(Map<String, Object> arguments) {
			List<Integer> sizeInLayers = new ArrayList<Integer>(_loop.size());
			int totalSize = 1;

			// 计算每一层的 size, 以及总 size
			for (List<String> x : _loop) {
				int size = 1;
				for (String argName : x) {
					Object arg = arguments.get(argName);
					List<Object> arrayArg = null;
					// 顺便对 arg 做一个类型处理
					if (arg == null) {
						// do nothing
					} else if (arg.getClass().isArray()) {
						arrayArg = Arrays.stream((Object[])arg).collect(Collectors.toList());
						arguments.replace(argName, arrayArg);
					} else if (arg instanceof List) {
						arrayArg = (List<Object>) arg;
					} else if (arg instanceof Collection) {
						arrayArg = new ArrayList<Object>((Collection<?>) arg);
						arguments.replace(argName, arrayArg);
					} else {
						arrayArg = new ArrayList<Object>(1);
						arrayArg.add(arguments.get(argName));
						arguments.replace(argName, arrayArg);
					}

					// 更新本层 size
					if (arrayArg != null && arrayArg.size() > size) {
						size = arrayArg.size();
					}
				}

				sizeInLayers.add(size);
				totalSize *= size;
			}

			List<Map<String, Object>> argMapArray = new ArrayList<>(totalSize);

			// 组装参数字典数组
			for (int i = 0; i < totalSize; ++i) {
				Map<String, Object> argMap = new HashMap<>(arguments);
				argMap.putAll(arguments);

				// 逐层处理
				for (int layer = 0; layer < _loop.size(); ++layer) {
					int size = sizeInLayers.get(layer);
					// 计算这一层中参数的偏移
					int offset = i % size;
					// 组装该层中每个参数的参数值
					for (String argName : _loop.get(layer)) {
						// 已经做完类型转换了，直接强制类型转换即可
						List<?> arrayArg = (List<?>) arguments.get(argName);
						if (arrayArg != null) {
							argMap.replace(argName, arrayArg.get(offset % arrayArg.size()));
						}
					}
				}
				argMapArray.add(argMap);
			}

			return argMapArray;
		}

		@SuppressWarnings("unchecked")
		@Override
		public void init(Map<String, Object> config, Map<String, JavaType> argumentTypes, SwaggerSqlAction next)
				throws Exception {
			_next = next;
			_key = (String) config.get("key");
			if (_key == null) {
				_key = "ans";
			}

			_argumentTypes = argumentTypes;

			Object sql = config.get("sql");
			if (sql == null) {
				throw new Exception("Fail to init(no sql text defined)");
			} else if (sql instanceof String) {
				_sqlTexts = new ArrayList<>();
				SqlScriptReader r = new SqlScriptReader(new StringReader((String) sql));
				while (true) {
					String s = r.readStatement();
					if (s == null) {
						break;
					}
					_sqlTexts.add(s);
				}
				r.close();
			} else if (sql instanceof List) {
				_sqlTexts = (List<String>) sql;
			} else {
				throw new Exception("Fail to init(invalid sql texts)");
			}

			Object loop = config.get("loop");
			if (loop != null) {
				initLoopConfig(loop);
			}
		}

		@SuppressWarnings("unchecked")
		private void initLoopConfig(Object loop) throws Exception {
			if (loop instanceof String) {
				_loop = new ArrayList<List<String>>();
				List<String> item = new ArrayList<String>();
				item.add((String) loop);
				_loop.add(item);

				return;
			} else if (loop instanceof List) {
				_loop = new ArrayList<List<String>>();
				for (Object val : (List<Object>) loop) {
					if (val instanceof String) {
						List<String> item = new ArrayList<String>();
						item.add((String) val);
						_loop.add(item);
					} else if (val instanceof List) {
						_loop.add((List<String>) val);
					} else {
						throw new Exception("Fail to init(invalid loop config 2)");
					}
				}
			} else {
				throw new Exception("Fail to init(invalid loop config 1)");
			}
		}

		@Override
		public Object execute(RequestContext request, Connection connection, Map<String, Object> arguments,
				Map<String, Object> context) throws Exception {
			List<Object> ret = new ArrayList<>();

			if (_loop != null) {
				// 循环执行
				for (Map<String, Object> argMap : getArgMapArray(arguments)) {
					List<Object> list = new ArrayList<>();
					for (String sqlText : _sqlTexts) {
						list.addAll(execSql(connection, sqlText, argMap, context));
					}

					ret.add(list);
				}
			} else {
				// 单次执行
				for (String sqlText : _sqlTexts) {
					ret.addAll(execSql(connection, sqlText, arguments, context));
				}
			}

			// 链式执行
			if (_next == null) {
				return ret;
			} else {
				context.put(_key, ret);
				return _next.execute(request, connection, arguments, context);
			}
		}

		private List<Object> execSql(Connection connection, String sqlText, Map<String, Object> args,
				Map<String, Object> context) throws SQLException {
			List<Object> ret = new ArrayList<>();

			// 构建 SQL 语句
			Sql sql = new SwaggerSqlNutSql(sqlText);

			// 计算变量值
			Map<String, Object> vars = new HashMap<>();
			for (String name : sql.varIndex().names()) {
				vars.put(name, calcVarValue(name, args, context));
			}
			sql.vars().putAll(vars);

			for (String name : sql.paramIndex().names()) {
				if (!vars.containsKey(name)) {
					vars.put(name, calcVarValue(name, args, context));
				}
			}
			sql.params().putAll(vars);

			_logger.info("\n{}\n", sql.forPrint());

			String pSql = sql.toPreparedStatement();
			List<String> orders = sql.paramIndex().getOrders();
			try (PreparedStatement statement = connection.prepareStatement(pSql);) {
				// 设置参数
				for (int i = 0; i < orders.size();) {
					Object value = vars.get(orders.get(i));
					statement.setObject(++i, value);
				}
				// 执行 SQL
				boolean isRS = statement.execute();
				visitSQLWarnings(statement.getWarnings());
				// 获取结果
				for (int i = 0;; ++i) {
					if (isRS) {
						// 访问记录集
						try (ResultSet resultSet = statement.getResultSet();) {
							ret.add(visitResultSet(i, resultSet));
						}
					} else {
						int updateCount = statement.getUpdateCount();
						if (updateCount != -1) {
							// 访问 update count
							ret.add(visitUpdateCount(i, updateCount));
						} else {
							// 没有结果集了
							break;
						}
					}

					// 取下一个记录集
					isRS = statement.getMoreResults();
				}
			}

			return ret;
		}

		/**
		 * 从上下文和参数中查找变量的值
		 */
		private Object calcVarValue(String name, Map<String, Object> args, Map<String, Object> context) {
			if (context.containsKey(name)) {
				return context.get(name);
			} else if (args.containsKey(name)) {
				return args.get(name);
			}

			String[] names = name.split("\\.");
			Object obj = null;
			if (context.containsKey(names[0])) {
				obj = context.get(names[0]);
			} else if (args.containsKey(names[0])) {
				obj = args.get(names[0]);
			}

			int layer = 0;
			while (obj != null && ++layer < names.length) {
				try {
					obj = obj.getClass().getField(names[layer]).get(obj);
				} catch (Exception e) {
					_logger.error("获取变量({})值失败: {}", name, obj.getClass().getName());
					obj = null;
				}
			}
			
			return obj;
		}

		protected Object visitResultSet(int index, ResultSet resultSet) throws SQLException {
			List<Map<String, Object>> ret = new ArrayList<>();
			ResultSetMetaData md = resultSet.getMetaData();

			// 逐条记录合并每个字段的值
			while (resultSet.next()) {
				visitSQLWarnings(resultSet.getWarnings());

				Map<String, Object> row = new HashMap<>();
				for (int column = 1; column <= md.getColumnCount(); ++column) {
					String label = md.getColumnLabel(column);
					Object value = resultSet.getObject(column);
					row.put(label, value);
				}
				ret.add(row);
			}

			return ret;
		}

		protected Object visitUpdateCount(int index, int updateCount) {
			return updateCount;
		}
	}

	static protected void visitSQLWarnings(SQLWarning sqlWarning) {
		while (sqlWarning != null) {
			_logger.warn("{ErrorCode {} - {}", sqlWarning.getErrorCode(), sqlWarning.getMessage());

			sqlWarning = sqlWarning.getNextWarning();
		}
	}
}